package model;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import javax.swing.DefaultComboBoxModel;
import javax.swing.JComboBox;

import dataClass.Adres_il;
import dataClass.Kisiler;


public class ConnectDB {
	private static Connection con = null;
	private static String userid ="root", password = "";
	private static String url = "jdbc:mysql://localhost:3306/osmanuna_med?useUnicode=true&characterEncoding=UTF-8";
	private static Statement stmt;
	private static ResultSet rSet = null;

	private static int _istenenDeger;
	private static boolean _isRow = false;
	private static int _returnedValue = 0;
	private static int _returnedID=0;
	private static ArrayList<String> _list = null;
	private static String istenenString=null;

	public static Connection getConnection()
	{
		try {
			Class.forName("com.mysql.jdbc.Driver");	//Class.forName("myDriver.ClassName"); ?

			con = DriverManager.getConnection(url,userid, password);
		} catch(SQLException ex) {
			System.err.println("SQLException: " + ex.getMessage());
		} catch(java.lang.ClassNotFoundException e) {
			System.err.print("ClassNotFoundException: ");
			System.err.println(e.getMessage());
		}		
		return con;
	}

	public static boolean executeCommand(String _sql,Connection _con){

		try {
			stmt = _con.createStatement();
			rSet = stmt.executeQuery(_sql);

			while (rSet.next()) {
				_isRow = true;					
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return _isRow;
	}

	public static boolean UpdateCommand(String _sql,Connection _con){

		try {
			stmt = _con.createStatement();			
			_returnedValue = stmt.executeUpdate(_sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return (_returnedValue>0);
	}

	public static int getMaxID(String _sql,Connection _con){
		try {
			stmt = _con.createStatement();			
			rSet = stmt.executeQuery(_sql);
			while (rSet.next()) {
				if(_returnedID<rSet.getInt(1)){
					_returnedID=rSet.getInt(1);
				}
			}	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return (_returnedID+1);
	}

	public static void comboBoxDoldur(JComboBox comboBox,String query,String ilkDeger){
		comboBox.setModel(new DefaultComboBoxModel(new String[] {ilkDeger}));

		try {
			setStmt(getConnection().createStatement());
			setrSet(getStmt().executeQuery(query));

			while (getrSet().next()) {
				comboBox.addItem(getrSet().getString(1));
			}
		} catch (Exception e) {
			// TODO: handle exception
		} finally {
			try {
				if(!getCon().isClosed()){
					getCon().close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	public static ArrayList<Object> getData(String query){
		ArrayList<Object> dataList = new ArrayList<Object>();

		try{
			stmt = con.createStatement();
			rSet = stmt.executeQuery(query);
			ResultSetMetaData meta = rSet.getMetaData();

			while(rSet.next()){
				for (int i = 1; i < meta.getColumnCount(); i++) {
					dataList.add(rSet.getObject(i));
				}

			}
		}catch (Exception e) {
			// TODO: handle exception
		}

		return dataList;
	}

	public static ArrayList<Kisiler> getKisiler(String query){
		ArrayList<Kisiler> tempKisiler = new ArrayList<Kisiler>();

		try {
			setStmt(getConnection().createStatement());
			setrSet(getStmt().executeQuery(query));

			while(getrSet().next()){
				Kisiler kisi = new Kisiler();
				kisi.setKisi_ID(getrSet().getInt(1));
				kisi.setAdi_1(getrSet().getString(2));
				kisi.setAdi_2(getrSet().getString(3));
				kisi.setSoyadi_1(getrSet().getString(4));
				kisi.setSoyadi_2(getrSet().getString(5));
				kisi.setE_posta_1(getrSet().getString(6));
				kisi.setMezuniyetYili(getrSet().getString(7));
				kisi.setBolum(getrSet().getString(8));
				kisi.setSehir(getrSet().getString(9));
				kisi.setIlce(getrSet().getString(10));

				tempKisiler.add(kisi);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try {
				if(!con.isClosed())
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return tempKisiler;
	}
	public static ArrayList<String> getDistinctData(String _sql,Connection _con,int _dataNumbers) throws SQLException{
		_list = new ArrayList<String>();
		try {
			stmt = _con.createStatement();			
			rSet = stmt.executeQuery(_sql);
			while (rSet.next()) {
				for(int i=1;i<=_dataNumbers;i++)
				{
					_list.add(rSet.getString(i));
				}
			}	
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			_con.close();
		}
		return _list;
	}

	public static void fillILComboBox(JComboBox comboBox){
		try {
			String query = "SELECT * FROM adres_il ORDER BY il_ID";
			stmt = getConnection().createStatement();
			rSet = stmt.executeQuery(query);

			while(rSet.next()){
				comboBox.addItem(new Adres_il(rSet.getString(2).trim()));
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				getCon().close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	public static String getDistinctStringDeger(String _sql){
		try {
			stmt = getConnection().createStatement();			
			rSet = stmt.executeQuery(_sql);
			while (rSet.next()) {
				istenenString = rSet.getString(1);
			}	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return istenenString;
	}

	public static int getDistinctIntdeger(String _sql,Connection _con)
	{
		try {
			stmt = _con.createStatement();			
			rSet = stmt.executeQuery(_sql);
			while (rSet.next()) {
				_istenenDeger = rSet.getInt(1);
			}	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				getCon().close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return _istenenDeger;
	}

	public static Connection getCon() {
		return con;
	}

	public static void setCon(Connection con) {
		ConnectDB.con = con;
	}

	public static Statement getStmt() {
		return stmt;
	}

	public static void setStmt(Statement stmt) {
		ConnectDB.stmt = stmt;
	}

	public static ResultSet getrSet() {
		return rSet;
	}

	public static void setrSet(ResultSet rSet) {
		ConnectDB.rSet = rSet;
	}
}
